Database Tutorials Window Functions এর মাধ্যমে Complex Query তৈরি গাইড ও নোট

SQL-এ Window Functions একটি শক্তিশালী টুল যা আপনাকে result set-এর প্রতি row-এ অ্যাগ্রিগেট ফাংশন প্রয়োগ করতে দেয়, তবে এটি গ্রুপিংয়ের পরিবর্তে উইন্ডো (অথবা ভাগ) ধারণা ব্যবহার করে। Window Functions ব্যবহারের মাধ্যমে আপনি খুব সহজে complex query তৈরি করতে পারেন, যেখানে আপনি একাধিক aggregation বা ranking তৈরি করতে পারবেন। এটি বিশেষভাবে প্রযোজ্য যখন আপনি একটি ডেটাবেসে complex analysis করতে চান।

Window Functions এর বেসিক ধারণা

Window Function সাধারণত একটি OVER() ক্লজের সঙ্গে ব্যবহৃত হয়। এই OVER() ক্লজটি একটি উইন্ডো নির্ধারণ করে, যার মধ্যে function টিকে প্রয়োগ করা হবে। উইন্ডোটি বিভিন্নভাবে কনফিগার করা যেতে পারে, যেমন:

• PARTITION BY: এটি একটি গ্রুপিং লজিক প্রদান করে, যার মাধ্যমে result set কে ভেঙে আলাদা আলাদা অংশে ভাগ করা হয়।
• ORDER BY: এটি উইন্ডোটি সঠিকভাবে সাজানোর জন্য ব্যবহৃত হয়, যাতে আপনি অ্যাগ্রিগেট ফাংশনকে একটি নির্দিষ্ট সাজান অনুসারে প্রয়োগ করতে পারেন।
• ROWS BETWEEN: এটি উইন্ডোর সীমানা কনফিগার করতে ব্যবহৃত হয়।

কিছু জনপ্রিয় Window Functions

• ROW_NUMBER(): প্রতিটি row এর জন্য একটি সারি নম্বর প্রদান করে।
• RANK(): প্রতিটি row এর জন্য র‍্যাঙ্ক প্রদান করে, তবে যদি একই মান থাকে, তাহলে একই র‍্যাঙ্ক হবে।
• DENSE_RANK(): র‍্যাঙ্কিং প্রদান করে, তবে একই মানের জন্য একই র‍্যাঙ্ক প্রদান করবে এবং পরবর্তী র‍্যাঙ্কে লাফিয়ে যাবে না।
• NTILE(n): একটি গ্রুপে নটি অংশে ডেটা ভাগ করে।
• LEAD() এবং LAG(): বর্তমান row থেকে পরবর্তী (LEAD) বা পূর্ববর্তী (LAG) row এর মান ফেরত দেয়।
• SUM(), AVG(), MIN(), MAX(): সাধারণ অ্যাগ্রিগেট ফাংশনগুলি উইন্ডো ফাংশনের সাথে ব্যবহৃত হতে পারে।

উদাহরণ

1. ROW_NUMBER() ব্যবহার করে Ranking তৈরি

SELECT 
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

এখানে, ROW_NUMBER() উইন্ডো ফাংশনটি প্রতিটি বিভাগে (department) কর্মচারীদের (employee) র‍্যাঙ্ক নির্ধারণ করবে, যেখানে salary অনুযায়ী তাদের সাজানো হবে (DESC অর্থাৎ উর্ধ্বমুখী শৃঙ্খলায়)।

2. RANK() এবং DENSE_RANK() এর ব্যবহার

SELECT 
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM 
    employees;

• RANK() একি salary থাকার ক্ষেত্রে কিছু র‍্যাঙ্ক বাদ দিয়ে পরবর্তী র‍্যাঙ্কে চলে যাবে।
• DENSE_RANK() একই salary-এর জন্য একই র‍্যাঙ্ক দিবে এবং পরবর্তী র‍্যাঙ্কে লাফিয়ে যাবে না।

3. LEAD() এবং LAG() এর মাধ্যমে পরবর্তী বা পূর্ববর্তী row এর মান দেখা

SELECT 
    employee_id,
    salary,
    LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary
FROM 
    employees;

এখানে, LEAD() এবং LAG() ব্যবহার করা হয়েছে যাতে বর্তমান row-এর পরবর্তী এবং পূর্ববর্তী row এর salary দেখানো যায়। LEAD() পরবর্তী row এর মান দেখাবে এবং LAG() পূর্ববর্তী row এর মান দেখাবে।

4. SUM() উইন্ডো ফাংশন দিয়ে চলমান মোট হিসাব করা

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM 
    employees;

এখানে, SUM(salary) উইন্ডো ফাংশনটি PARTITION BY department দিয়ে বিভাগ অনুসারে এবং ORDER BY salary দিয়ে salary অনুযায়ী সাজানো হয়েছে। ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW এর মাধ্যমে, এটি গত row থেকে চলমান মোট হিসাব করবে।

SQLAlchemy তে Window Functions

SQLAlchemy-তে Window Functions ব্যবহার করার জন্য, আপনি func বা over মেথডের মাধ্যমে এগুলি ব্যবহার করতে পারেন। নীচে একটি উদাহরণ দেখানো হলো:

from sqlalchemy import create_engine, Column, Integer, String, Float, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import select, func

Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'
    employee_id = Column(Integer, primary_key=True)
    department = Column(String)
    salary = Column(Float)

# Create engine and session
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Query with Window Function
from sqlalchemy import func

stmt = (
    select(
        Employee.employee_id,
        Employee.department,
        Employee.salary,
        func.row_number().over(
            partition_by=Employee.department, 
            order_by=Employee.salary.desc()
        ).label('salary_rank')
    )
)

result = session.execute(stmt).fetchall()
for row in result:
    print(row)

এখানে, SQLAlchemy-তে ROW_NUMBER() উইন্ডো ফাংশন ব্যবহার করা হয়েছে। partition_by দিয়ে ডেটাকে বিভাগ অনুযায়ী ভাগ করা হয়েছে এবং order_by দিয়ে salary অনুযায়ী সাজানো হয়েছে।

উপসংহার

Window Functions SQL-এর একটি শক্তিশালী ফিচার, যা complex query তৈরি করতে ব্যবহৃত হয়। আপনি যখন অতিরিক্ত অ্যানালিটিক্যাল কাজ করতে চান, যেমন র‍্যাঙ্কিং, চলমান মোট বা পরবর্তী/পূর্ববর্তী মান বের করা, তখন এই ফাংশনগুলি খুবই উপকারী। SQLAlchemy ব্যবহার করলে, আপনি এই উইন্ডো ফাংশনগুলি সহজেই ব্যবহার করতে পারেন এবং তাদের সাহায্যে জটিল queries তৈরি করতে পারেন।